﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using UniMana.Data;

namespace UniMana.Data
{
    public static class ReportStudent_GenderData_Chi
    {
        //public static IQueryable GetStudentGender(this Table<Training_TrainingProgram> nTrainingProgram, int _iMajorKey)
        //{
        //    var trainingProgram = nTrainingProgram.Where(p => p.Catalogue_MajorKey == _iMajorKey);
        //    return trainingProgram;
        //}

        public static IQueryable GetStudent_Gender(this  Table<Training_Student> student,
                       Table<Catalogue_Major> cataMajor,
                       Table<Training_Student_Class> student_Class,
                       Table<Catalogue_Level> cataLevel,
                       Table<Catalogue_Department> cataDep,
                       Table<Catalogue_TrainingType> cataTraType,
                       Table<Catalogue_Course> cataCourse,int _iCou, int _iLevel, int _iDep, int _iMajor, int _iTraType)
        {
            var view = from t in student_Class
                        join t0 in cataCourse on new { Catalogue_CourseKey = Convert.ToInt32(t.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t0.Catalogue_CourseKey }
                        join t1 in cataMajor on t.Catalogue_MajorKey equals t1.Catalogue_MajorKey
                        join t01 in cataDep on new { Catalogue_DepartmentKey = Convert.ToInt32(t1.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t01.Catalogue_DepartmentKey }
                        join t2 in student on t.Training_StudentKey equals t2.Training_StudentKey
                        join t3 in cataLevel on new { Catalogue_LevelKey = Convert.ToInt32(t.Catalogue_LevelKey) } equals new { Catalogue_LevelKey = t3.Catalogue_LevelKey }
                        join t4 in cataTraType on new { Catalogue_TrainingTypeKey = Convert.ToInt32(t.Catalogue_TrainingTypeKey) } equals new { Catalogue_TrainingTypeKey = t4.Catalogue_TrainingTypeKey }
                        group new { t0, t01, t1, t2, t3, t4 } by new
                        {
                            t0.Catalogue_CourseKey,
                            t0.Name,
                            t01.Catalogue_DepartmentKey,
                            Column1 = t01.Name,
                            t1.Catalogue_MajorKey,
                            t1.ID,
                            Column2 = t1.Name,
                            t2.Gender,
                            t3.Catalogue_LevelKey,
                            Column3 = t3.Name,
                            t4.Catalogue_TrainingTypeKey,
                            Column4 = t4.Name
                        } into g
                        //where g.Key.Catalogue_CourseKey == _iCou &&
                        //  g.Key.Catalogue_DepartmentKey == _iDep &&
                        //  g.Key.Catalogue_MajorKey == _iMajor &&
                        //  g.Key.Catalogue_LevelKey == _iLevel &&
                        //  g.Key.Catalogue_TrainingTypeKey == _iTraType
                        select new
                        {
                            KeyKhoaHoc = (System.Int32?)g.Key.Catalogue_CourseKey,
                            TenKhoaHoc = g.Key.Name,
                            KeyKHoa = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                            TenKhoa = g.Key.Column1,
                            KeyNganh = (System.Int32?)g.Key.Catalogue_MajorKey,
                            MaNganh = g.Key.ID,
                            TenNganh = g.Key.Column2,
                            KeyBDT = (System.Int32?)g.Key.Catalogue_LevelKey,
                            TenBDT = g.Key.Column3,
                            KeyLHDT = (System.Int32?)g.Key.Catalogue_TrainingTypeKey,
                            TenLHDT = g.Key.Column4,
                            //Gender = (System.Boolean?)g.Key.Gender,
                            Gender = g.Key.Gender == true ? "Nữ" : "Nam",
                            TongSoSV = (Int64?)g.Count(),
                           
                        };
            if (_iDep > 0)
            {
                view = view.Where(p => p.KeyKHoa == int.Parse(_iDep.ToString()));
            }
            if (_iMajor > 0)
            {
                view = view.Where(p => p.KeyNganh == int.Parse(_iMajor.ToString()));
            }
            if (_iTraType > 0)
            {
                view = view.Where(p => p.KeyLHDT == int.Parse(_iTraType.ToString()));
            }
            if (_iCou > 0)
            {
                view = view.Where(p => p.KeyKhoaHoc == int.Parse(_iCou.ToString()));
            }
            if (_iLevel > 0)
            {
                view = view.Where(p => p.KeyBDT == int.Parse(_iLevel.ToString()));
            }
            return view.Distinct();
        }
    }
}
